package com.topisv.tms.repository.stock;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import com.topisv.tms.entity.stock.CusStock;

/**
 * 商家库存_dao
 * @author ch
 *
 */
public abstract interface CusStockDao extends PagingAndSortingRepository<CusStock, Long>, JpaSpecificationExecutor<CusStock>
{

	@Query("select t from CusStock t where t.warehouseCode=?1 and t.customerCode=?2 and t.sku=?3")
	List<CusStock> loadStock(String warehouseCode,String customerCode, String sku);
	
	@Query("select t from CusStock t where t.warehouseCode=?1 and t.customerCode=?2 and t.sku=?3 and t.salesNum > 0 order by salesNum asc,stockCode asc")
	List<CusStock> loadStockOrderByStockCodeAndSalesNum(String warehouseCode,String customerCode, String sku);
	
	@Query("select t from CusStock t where t.customerCode=? and t.sku=?")
	List<CusStock> loadStock(String customerCode, String sku);
	
	@Query("select t from CusStock t where t.customerCode=? and t.warehouseCode=? order by t.stockCode")
	List<CusStock> loadStockByCustomerAndWarehouse(String customerCode, String warehouseCode);

	@Query("select t from CusStock t where t.customerCode=?2 and t.warehouseCode=?1 and t.boxCode=?3")
	List<CusStock> loadStockByBoxCode(String warehouseCode, String customerCode, String boxCode);

	@Query("select t from CusStock t where t.customer.customerCode=?1 and t.warehouseCode=?2 and sku=?3 order by t.stockCode")
	List<CusStock> loadStockByCondition(String customerCode,String warehouseCode, String sku);

	@Query("select t from CusStock t where t.customerCode=?1 and t.warehouseCode=?2 and t.stockCode=?3 and t.boxCode=?4 and sku=?5")
	CusStock loadStock(String customerCode, String warehouseCode,String stockCode, String boxCode, String sku);
	
	@Query("select t from CusStock t where t.customerCode=?1 and t.warehouseCode=?2 and t.stockCode=?3 and t.boxCode=?4")
    List<CusStock> loadStockNew(String customerCode, String warehouseCode,String stockCode, String boxCode);
	
	@Query("select t from CusStock t where t.customerCode=?1 and t.warehouseCode=?2 and t.stockCode=?3 and t.boxCode=?4 and sku=?5 and "
	    + "(batchCode = ?6 or ?6 is null or ?6 = '') and (productDate = ?7 or ?7 is null or ?7 = '') and "
	    + "(expireDate = ?8 or ?8 is null or ?8 = '')")
    CusStock loadStockNew2(String customerCode, String warehouseCode,String stockCode, String boxCode, String sku, 
        String batchCode, String productDate, String expireDate);
	
	/**
	 * 判断改库位上是不是存在其他的商品，并且数量>0
	 * AddBy yinglei_lian 2016.09.27
	 * @return
	 */
	@Query("select t from CusStock t where t.customerCode=?1 and t.warehouseCode=?2 and t.stockCode=?3 and t.boxCode=?4 and sku!=?5 and (t.salesNum>0 or t.unusedNum>0 or t.orderNum>0 or t.lockedNum>0 or t.transportNum>0 or t.airNum>0)")
	List<CusStock> LoadOtherStock(String customerCode, String warehouseCode,String stockCode, String boxCode, String sku);
	
	@Query("select t from CusStock t where t.warehouseCode=?1 and t.stockCode=?2 and t.boxCode=?3 and sku=?4")
	List<CusStock> loadStockNoCustomerCode(String warehouseCode,String stockCode, String boxCode, String sku);

	@Query("select t from CusStock t where  t.stockCode=?")
	List<CusStock> loadByStockCode(String stockCode);

	@Query("select t from CusStock t where  t.customerCode=?")
	List<CusStock> loadByCustomerCode(String customerCode);

	@Query("select t from CusStock t where t.customerCode=? and t.warehouseCode=? and t.sku=? and t.boxType=?")
	List<CusStock> loadStock(String customerCode, String warehouseCode,String sku, String boxType);
	
	@Modifying
	@Query("update CusStock set lockedNum=lockedNum+salesNum,salesNum=0 where id in ?1")
	void lockStock(List<Long>  ids);
	
	@Modifying
	@Query("update CusStock set salesNum=lockedNum+salesNum,lockedNum=0 where id in ?1")
	void unLockStock(List<Long>  ids);
	
	@Modifying
	@Query(nativeQuery=true, value="update cus_stock set order_num=order_num-?6, version=version+?7 where customer_code=?1 and warehouse_code=?2 and stock_code=?3 and box_code=?4 and sku=?5 and order_num>=?6")
	int updateCusStock4Pick(String customerCode, String warehouseCode, String stockCode, String boxCode, String sku, int num, int versionIncre);
}